package com.skyline.spot.dao.impl;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.util.List;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Repository;

import com.skyline.base.dao.impl.BaseDaoImpl;
import com.skyline.base.type.Activity;
import com.skyline.base.type.SpotType;
import com.skyline.common.bean.Page;
import com.skyline.spot.dao.SpotDao;
import com.skyline.spot.mapper.SpotMapper;
import com.skyline.spot.model.Spot;

@Repository("spotDao")
public class SpotDaoImpl extends BaseDaoImpl implements SpotDao {
	/**
	 * insert
	 */
	/**
	 * insert into spot (name, portrait, category, creatorid, creatornickname,
	 * creatorportrait, creatoremail, administratorid, administratornickname,
	 * administratorportrait, administratoremail, country, province, city,
	 * siteid, detailaddress, latitude, longitude, createtime, updatetime,
	 * content) values ( ?, ? , ? , ? , ? , ? , ? , ? , ? , ? , ?, ? , ? , ? , ?
	 * , ? , ? , ? , current_timestamp,current_timestamp,? )
	 */
	@Value("${SpotDao.insertSpot}")
	private String insertSpotSQL;
	/**
	 * update spot set name=? ,category=? country=? , province=? , city=? ,
	 * siteid=? , detailaddress=? , positionX=? , positionY=? ,
	 * updatetime=current_timestamp where id=? activity=?
	 */
	@Value("${SpotDao.updateSpot}")
	private String updateSpotSQL;
	/**
	 * update spot set content=? , updatetime=current_timestamp where id=? and
	 * activity=?
	 */
	@Value("${SpotDao.updateSpotIntroduction}")
	private String updateSpotIntroductionSQL;

	/**
	 * update spot set portrait=? , updatetime=current_timestamp where id=? and
	 * activity=?
	 */
	@Value("${SpotDao.updateSpotPortrait}")
	private String updateSpotPortraitSQL;
	/**
	 * update spot set characteristic= ? where where id=? and activity=?
	 */
	@Value("${SpotDao.updateSpotCharacteristic}")
	private String updateSpotCharacteristicSQL;
	/**
	 * update spot set star=? , votes=? , updatetime=current_timestamp where
	 * id=? and activity=?
	 */
	@Value("${SpotDao.updateSpotStar}")
	private String updateSpotStarSQL;

	@Value("${SpotDao.updateSpotVistNum}")
	private String updateSpotVistNumSQL;
	/**
	 * update spot set administratorid=? , administratornickname=? ,
	 * administratorportrait=? , administratoremail=? ,
	 * updatetime=current_timestamp where id=? and activity=?
	 */
	@Value("${SpotDao.updateSpotAdmin}")
	private String updateAdminSQL;
	/**
	 * delete
	 */
	/**
	 * update spot set activity ='DELETED' , updatetime =current_timestamp where
	 * id=?
	 */
	@Value("${SpotDao.deleteSpot}")
	private String deleteSporSQL;
	/**
	 * query
	 */
	/**
	 * select * from spot
	 */
	@Value("${SpotDao.queryAll}")
	private String queryAllSQL;
	/**
	 * select * from spot where activity=?
	 */
	@Value("${SpotDao.queryAllByActivity}")
	private String queryAllByActivitySQL;
	/**
	 * select * from spot where category = ? and activity = ?
	 */
	@Value("${SpotDao.querySpotsByType}")
	private String querySpotsByTypeSQL;

	/**
	 * select * from spot where id=?
	 */
	@Value("${SpotDao.querySpotById}")
	private String querySpotByIdSQL;
	/**
	 * select * from spot where id=? and activity=?
	 */
	@Value("${SpotDao.querySpotByIdAndActivity}")
	private String querySpotByIdAndActivitySQL;

	/**
	 * select * from spot where left(siteid,?)=?
	 */
	@Value("${SpotDao.querySpotsBySiteId}")
	private String querySpotsBySiteIdSQL;
	/**
	 * select * from spot where left(siteid,?)=? and category=?
	 */
	@Value("${SpotDao.querySpotsBySiteIdAndType}")
	private String querySpotsBySiteIdAndTypeSQL;
	/**
	 * select * from spot where left(siteid,?)=? and activity=?
	 */
	@Value("${SpotDao.querySpotsBySiteIdAndActivity}")
	private String querySpotsBySiteIdAndActivitySQL;
	/**
	 * select * from spot where siteid left(siteid,?)=? and category = ? and
	 * activity=?
	 */
	@Value("${SpotDao.querySpotsBySiteIdAndTypeAndActivity}")
	private String querySpotsBySiteIdAndTypeAndActivitySQL;

	/**
	 * select * from spot where country =? and category = ? and activity=?
	 */
	@Value("${SpotDao.querySpotsByCountry}")
	private String querySpotsByCountrySQL;
	/**
	 * select * from spot where province=? and category = ? and activity=?
	 */
	@Value("${SpotDao.querySpotsByPrivonce}")
	private String querySpotsByPorvinceSQL;
	/**
	 * select * from spot where city=? and category = ? and activity=?
	 */
	@Value("${SpotDao.querySpotsByCity}")
	private String querySpotsByCitySQL;
	/**
	 * select * from spot where name = ?
	 */
	@Value("${SpotDao.querySpotsByName}")
	private String querySpotsByNameSQL;
	/**
	 * select * from spot where name = ? and category =?
	 */
	@Value("${SpotDao.querySpotsByNameAndType}")
	private String querySpotsByNameAndTypeSQL;
	/**
	 * select * from spot where name = ? and activity =?
	 */
	@Value("${SpotDao.querySpotsByNameAndActivity}")
	private String querySpotsByNameAndyActivitySQL;
	/**
	 * select * from spot where name = ? and category = ? and activity=?
	 */
	@Value("${SpotDao.querySpotsByNameAndTypeAndActivity}")
	private String querySpotsByNameAndTypeAndActivitySQL;

//	/**
//	 * select * from spot where name like ?
//	 */
//	@Value("${SpotDao.querySpotsByLikeName}")
//	private String querySpotsByLikeNameSQL;
	/**
	 * select * from spot where name like ? and category =?
	 */
	@Value("${SpotDao.querySpotsByLikeNameAndType}")
	private String querySpotsByLikeNameAndTypeSQL;
	/**
	 * select * from spot where name like ? and activity =?
	 */
	@Value("${SpotDao.querySpotsByLikeNameAndActivity}")
	private String querySpotsByLikeNameAndyActivitySQL;
	/**
	 * select * from spot where name like ? and category = ? and activity=?
	 */
	@Value("${SpotDao.querySpotsByLikeNameAndTypeAndActivity}")
	private String querySpotsByLikeNameAndTypeAndActivitySQL;
	/**
	 * select * from spot where id=? and activity=?
	 */
	@Value("${SpotDao.queryStarAndVotesNumById}")
	private String queryStarAndVotesNumSQL;
	/**
	 * select * from spot oder by star
	 */
//	@Value("${SpotDao.querySpotsOderByStar}")
//	private String quertSpotsOderByStar;
//	/**
//	 * select * from spot where activity=? oder by star
//	 */
//	@Value("${SpotDao.querySpotsByActivityOderByStar}")
//	private String querySpotsByActivityOderByStar;
//	/**
//	 * select * from spot where category =? oder by star
//	 */
//	@Value("${SpotDao.querySpotsByTypeOderByStar}")
//	private String querySpotsByTypeOderByStar;
//	/**
//	 * select * from spot where category =? and activity=? oder by star
//	 */
//	@Value("${SpotDao.querySpotsByTypeAndActicityOderByStar}")
//	private String querySpotsByTypeAndActivityOderByStar;

	@Override
	public Spot querySpotById(Long spotId, Activity activity) {
		List<Spot> list = null;
		try {
			if (activity == null) {
				list = jdbcTemplate.query(querySpotByIdSQL,
						SpotMapper.getMapper(), spotId);
			} else {
				list = jdbcTemplate.query(querySpotByIdAndActivitySQL,
						SpotMapper.getMapper(), spotId, activity.toString());
			}
		} catch (Exception e) {
			return null;
		}
		if (list.isEmpty())
			return null;
		else
			return list.get(0);
	}

	@Override
	public List<Spot> querySpotsAll(SpotType type, Page page, Activity activity) {
		List<Spot> list = null;
		if (type == null && page == null) {
			list = jdbcTemplate.query(queryAllSQL, SpotMapper.getMapper());
		} else if (type == null) {
			list = this.getPaginationResult(queryAllByActivitySQL, page,
					SpotMapper.getMapper(), Activity.NORMAL.toString());
		} else if (page == null) {
			list = jdbcTemplate.query(queryAllByActivitySQL,
					SpotMapper.getMapper(), activity.toString());
		} else {
			list = this.getPaginationResult(querySpotsByTypeSQL, page,
					SpotMapper.getMapper(), type.toString(),
					activity.toString());
		}

		return list;

	}

	@Override
	public List<Spot> querySpotsBySiteIdAndType(String siteId, int subLen,
			SpotType type, Page page, Activity activity) {
		List<Spot> list = null;
		if (page != null) {
			if (type == null && activity == null)
				this.getPaginationResult(querySpotsBySiteIdSQL, page,
						SpotMapper.getMapper(), subLen, siteId);
			else if (type == null)
				this.getPaginationResult(querySpotsBySiteIdAndActivitySQL,
						page, SpotMapper.getMapper(), subLen, siteId,
						activity.toString());
			else if (activity == null)
				this.getPaginationResult(querySpotsBySiteIdAndTypeSQL, page,
						SpotMapper.getMapper(), subLen, siteId, type.toString());

			else
				list = this.getPaginationResult(
						querySpotsBySiteIdAndTypeAndActivitySQL, page,
						SpotMapper.getMapper(), subLen, siteId,
						type.toString(), activity.toString());
		}
		return list;
	}

	@Override
	public List<Spot> querySpotsByName(String name, SpotType type,
			Activity activity) {
		if (activity != null && type != null) {
			return jdbcTemplate.query(querySpotsByNameAndTypeAndActivitySQL,
					SpotMapper.getMapper(), name, type.toString(),
					Activity.NORMAL.toString());
		}
		if (activity == null && type == null) {
			return jdbcTemplate.query(querySpotsByNameSQL,
					SpotMapper.getMapper(), name);
		} else if (type == null) {
			return jdbcTemplate.query(querySpotsByNameAndyActivitySQL,
					SpotMapper.getMapper(), name, Activity.NORMAL.toString());
		} else if (activity == null) {
			return jdbcTemplate.query(querySpotsByNameAndTypeSQL,
					SpotMapper.getMapper(), name, type.toString());
		}
		return null;

	}

	@Override
	public List<Spot> querySpotByLikeName(String likeName, SpotType type,
			Page page, Activity activity) {
		List<Spot> list = null;
		likeName = "%" + likeName + "%";
		if (type != null && activity != null)
			list = this.getPaginationResult(
					querySpotsByLikeNameAndTypeAndActivitySQL, page,
					SpotMapper.getMapper(), likeName, type.toString(),
					activity.toString());
		else if (type == null)
			list = this.getPaginationResult(
					querySpotsByLikeNameAndyActivitySQL, page,
					SpotMapper.getMapper(), likeName, activity.toString());
		else
			list = this.getPaginationResult(querySpotsByLikeNameAndTypeSQL,
					page, SpotMapper.getMapper(), likeName, type.toString());
		return list;
	}

	@Override
	public List<Spot> querySpotsByConntryAndType(String countryName,
			SpotType type, Page page, Activity activity) {
		// todo 对分类查找还没有实现
		return this.getPaginationResult(querySpotsByCountrySQL, page,
				SpotMapper.getMapper(), countryName, type.toString(),
				activity.toString());

	}

	@Override
	public List<Spot> querySpotsByProvinceAndType(String provinceName,
			SpotType type, Page page, Activity activity) {
		return this.getPaginationResult(querySpotsByPorvinceSQL, page,
				SpotMapper.getMapper(), provinceName, type.toString(),
				activity.toString());
	}

	@Override
	public List<Spot> querySpotsByCityAndType(String cityName, SpotType type,
			Page page, Activity activity) {
		return this.getPaginationResult(querySpotsByCitySQL, page,
				SpotMapper.getMapper(), cityName, type.toString(),
				activity.toString());
	}

	/*************************************** 更新and插入 *****************************************/
	@Override
	public long insertSpot(String name, SpotType type, Long creatorId,
			String creatorNickName, String creatorPortrait,
			String creatorEmail, String country, String province, String city,
			String siteId, String detailAddress, Double lat, Double lng,
			String content) {
		return this.insertWithIdReturn(insertSpotSQL, name, type.toString(),
				creatorId, creatorNickName, creatorPortrait, creatorEmail,
				creatorId, creatorNickName, creatorPortrait, creatorEmail,
				country, province, city, siteId, detailAddress, lat, lng,
				content);
	}

	@Override
	public void updateSpotIntroduction(Long spotId, String content) {
		jdbcTemplate.update(updateSpotIntroductionSQL, content, spotId,
				Activity.NORMAL.toString());

	}

	@Override
	public void updateSpotPortrait(Long spotId, String portrait) {
		jdbcTemplate.update(updateSpotPortraitSQL, portrait, spotId,
				Activity.NORMAL.toString());

	}

	@Override
	public void updateSpotCharacteristic(Long spotId, String characteristic) {
		jdbcTemplate.update(updateSpotCharacteristicSQL, characteristic,
				spotId, Activity.NORMAL.toString());

	}

	@Override
	public Spot updateSpotStar(Long spotId, Integer star) {
		// TODO Auto-generated method stub
		List<Spot> s = jdbcTemplate.query(queryStarAndVotesNumSQL,
				SpotMapper.getMapper(), spotId, Activity.NORMAL.toString());
		float db_star = s.get(0).getStar();
		Integer db_votes = s.get(0).getVotes();
		float newstar = (db_star * db_votes + star) / (db_votes + 1);
		jdbcTemplate.update(updateSpotStarSQL, newstar, spotId,
				Activity.NORMAL.toString());
		s.get(0).setStar(newstar);
		s.get(0).setVotes(db_votes + 1);
		return s.get(0);

	}

	@Override
	public Spot updateSpotVistNum(Long spotId) {
		jdbcTemplate.update(updateSpotVistNumSQL, spotId);
		return null;
	}

	@Override
	public void updateSpotAdministrator(Long spotId, Long administratorId,
			String administratorNickName, String administratorEmail,
			String administratorPortrait) {
		jdbcTemplate.update(updateAdminSQL, administratorId,
				administratorNickName, administratorEmail,
				administratorPortrait, spotId, Activity.NORMAL.toString());

	}

	@Override
	public void updateSpot(Long spotId, String name, SpotType type,
			String country, String province, String city, String siteId,
			String detailAddress, Double lat, Double lng) {
		jdbcTemplate.update(updateSpotSQL, name, type.toString(), country,
				province, city, siteId, detailAddress, lat, lng, spotId,
				Activity.NORMAL.toString());
	}

	@Override
	public void deleteSpot(Long spotId) {
		jdbcTemplate.update(deleteSporSQL, Activity.DELETED.toString(), spotId);

	}

	@Override
	public void recoverSpot(Long spotId) {
		// TODO Auto-generated method stub

	}

}
